import openpyxl
import tools_pmc

# 打开工作簿
wb_bom = openpyxl.load_workbook(tools_pmc.lj_bom)
ws_bom = wb_bom.worksheets[0]

wb_mc = openpyxl.load_workbook(tools_pmc.lj_mc)
ws_mc = wb_mc.worksheets[0]
table_array_mc = list(ws_mc['A2':'BB' + str(ws_mc.max_row)])

dict_col = {"在途数量": None,
            "在检数量": None,
            "已请未购数量": None,
            "在库超365天": None,
            "预计消耗周期": None,
            "365天销货量": None,
            "365天生产使用量": None,
            "365天消耗量": None,
            "可用数量": None,
            "进货数量": None,
            "工销比": None}
for i in range(1, ws_mc.max_column + 1):
    if ws_mc.cell(1, i).value in dict_col.keys():
        dict_col[ws_mc.cell(1, i).value] = i

wb = openpyxl.load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\2，呆滞料管理\呆滞原材料处理台账.xlsx")
ws = wb.worksheets[0]

for x in range(2, ws.max_row + 1):
    ws.cell(x, 4).value = (tools_pmc.vlookup(ws.cell(x, 1).value, table_array_mc, dict_col["可用数量"]) +
                           tools_pmc.vlookup(ws.cell(x, 1).value, table_array_mc, dict_col["在途数量"]) +
                           tools_pmc.vlookup(ws.cell(x, 1).value, table_array_mc, dict_col["在检数量"]) +
                           tools_pmc.vlookup(ws.cell(x, 1).value, table_array_mc, dict_col["已请未购数量"]))
    ws.cell(x, 5).value = tools_pmc.vlookup(ws.cell(x, 1).value, table_array_mc, dict_col["在库超365天"])
    ws.cell(x, 6).value = tools_pmc.vlookup(ws.cell(x, 1).value, table_array_mc, dict_col["预计消耗周期"])
    set1 = set()
    for i in range(2, ws_bom.max_row + 1):
        if ws_bom.cell(i, 3).value == str(ws.cell(x, 1).value):
            set1.add(ws_bom.cell(i, 1).value)

    set2 = set()
    set3 = set()
    for i in set1:

        if i[0] == "3":
            set3.add(i)
            for j in range(2, ws_bom.max_row + 1):
                if ws_bom.cell(j, 1).value == i:
                    set2.add(ws_bom.cell(j, 2).value)
        else:
            for j in range(2, ws_bom.max_row + 1):
                if ws_bom.cell(j, 3).value == i:
                    set3.add(ws_bom.cell(j, 1).value)
                    set2.add(ws_bom.cell(j, 2).value)
    temp = ""
    for i in set2:
        temp += i + "、\n"
    temp = temp[:-2]
    ws.cell(x, 13).value = temp

    list_start1_1 = tools_pmc.bom_start_1(ws_bom)
    set4 = set()
    temp = [0, 0]
    for i in set3:
        list1, list2 = tools_pmc.bom_1(i, ws_bom)
        for j in list1:
            if j[0:8] == "10101001":
                set4.add(j)
        temp[0] += list2[list1.index(ws.cell(x, 1).value)]
        temp[1] += 1
    if temp[1] is not None and temp[1] != 0:
        ws.cell(x, 8).value = temp[0] / temp[1]
    else:
        ws.cell(x, 8).value = 0

    # 传感器品号
    # temp = ""
    # for i in set4:
    #     temp += i + "、\n"
    # temp = temp[:-2]
    # # print(temp)

    # 传感器总库存
    temp = 0
    for i in set4:
        temp += tools_pmc.vlookup(i, tools_pmc.table_array_wlxxb, 14)
    ws.cell(x, 7).value = temp

    ws.cell(x, 9).value = ws.cell(x, 8).value * ws.cell(x, 7).value
    if ws.cell(x, 10).value is None:
        ws.cell(x, 10).value = 0
    ws.cell(x, 11).value = ws.cell(x, 4).value - ws.cell(x, 9).value - ws.cell(x, 10).value

wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\2，呆滞料管理\呆滞原材料处理台账.xlsx")
